import mysql.connector
from mysql.connector import Error
from typing import List, Dict, Any
import os


class MySQLClient:
    def __init__(self):
        self.connection = self._create_connection()

    def _create_connection(self):
        """创建数据库连接"""
        try:
            return mysql.connector.connect(
                host=os.getenv("MYSQL_HOST", "localhost"),
                port=os.getenv("MYSQL_PORT", 3306),
                user=os.getenv("MYSQL_USER", "root"),
                password=os.getenv("MYSQL_PASSWORD", ""),
                database=os.getenv("MYSQL_DB", "gov_ai_db")
            )
        except Error as e:
            print(f"数据库连接错误: {e}")
            return None

    def execute_query(self, query: str, params: tuple = None) -> List[Dict]:
        """执行查询语句"""
        cursor = self.connection.cursor(dictionary=True)
        try:
            cursor.execute(query, params or ())
            return cursor.fetchall()
        except Error as e:
            print(f"查询执行错误: {e}")
            return []
        finally:
            cursor.close()

    def execute_update(self, query: str, params: tuple = None) -> bool:
        """执行更新/插入语句"""
        cursor = self.connection.cursor()
        try:
            cursor.execute(query, params or ())
            self.connection.commit()
            return True
        except Error as e:
            print(f"更新执行错误: {e}")
            self.connection.rollback()
            return False
        finally:
            cursor.close()

    def insert_data(self, table: str, data: Dict) -> bool:
        """通用数据插入方法"""
        columns = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        return self.execute_update(query, tuple(data.values()))

    def search_data(self, table: str, conditions: Dict, limit: int = 5) -> List[Dict]:
        """带条件的搜索方法"""
        where_clause = " AND ".join([f"{k} = %s" for k in conditions.keys()])
        query = f"SELECT * FROM {table} WHERE {where_clause} ORDER BY timestamp DESC LIMIT %s"
        params = tuple(conditions.values()) + (limit,)
        return self.execute_query(query, params)

    def insert_crawled_item(self, item_data: dict) -> bool:
        """插入爬取的事项数据（主表+内容表事务操作）"""
        try:
            with self.connection.cursor() as cursor:
                # 插入主表
                main_sql = """INSERT INTO government_items (item_code, item_name)
                              VALUES (%s, %s)
                              ON DUPLICATE KEY UPDATE item_name=VALUES(item_name)"""
                cursor.execute(main_sql, (item_data['item_code'], item_data['item_name']))

                # 插入内容表
                content_sql = """INSERT INTO item_contents (item_code, content)
                                 VALUES (%s, %s)
                                 ON DUPLICATE KEY UPDATE content=VALUES(content)"""
                cursor.execute(content_sql, (item_data['item_code'], item_data['content']))

            self.connection.commit()
            return True
        except Error as e:
            print(f"事项数据插入失败: {e}")
            self.connection.rollback()
            return False

    def get_item_content(self, item_code: str) -> dict:
        """根据事项编码获取完整数据"""
        query = """
            SELECT i.item_code, i.item_name, c.content 
            FROM government_items i
            JOIN item_contents c ON i.item_code = c.item_code
            WHERE i.item_code = %s
        """
        result = self.execute_query(query, (item_code,))
        return result[0] if result else None

    def search_items_by_keyword(self, keyword: str, limit: int = 10) -> List[dict]:
        """根据关键词搜索事项内容"""
        query = """
            SELECT i.item_code, i.item_name, 
                   MATCH(c.content) AGAINST(%s IN NATURAL LANGUAGE MODE) AS relevance
            FROM item_contents c
            JOIN government_items i ON c.item_code = i.item_code
            WHERE MATCH(c.content) AGAINST(%s IN NATURAL LANGUAGE MODE)
            ORDER BY relevance DESC
            LIMIT %s
        """
        return self.execute_query(query, (keyword, keyword, limit))
